﻿Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Data
Imports System.Data.OracleClient
Imports System.Configuration
Imports System.Collections
Imports BusinessRule.DTO
Imports System.Xml

Public MustInherit Class DatabaseHelper
    Private Shared Function GetConntionSource() As String
        Dim strDBHostName, strDBName, strDBPort As String
        Dim strDBUser, strDBPassword As String

        Dim configXml As New XmlDocument
        Dim configPath = My.Computer.Registry.GetValue("HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Camstar\Camstar InSite Admin\Management Studio", "ConfigurationFile", Nothing)

        configXml.Load(configPath)
        Dim config As XmlNode = configXml.SelectSingleNode("Sites/Site/TransactionDBConfig")
        Dim attNode As XmlNode = config.SelectSingleNode("DBHostName")
        strDBHostName = attNode.InnerText
        attNode = config.SelectSingleNode("DBName")
        strDBName = attNode.InnerText
        attNode = config.SelectSingleNode("ConnectionParameters")
        strDBPort = attNode.InnerText.Replace("Port=", "")
        attNode = config.SelectSingleNode("User")
        strDBUser = attNode.InnerText
        attNode = config.SelectSingleNode("Password")
        strDBPassword = Camstar.Util.CryptUtil.Decrypt(attNode.InnerText)
        GetConntionSource = String.Format("Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = {0})" +
                                          "(PORT = {1})))(CONNECT_DATA =(SERVICE_NAME = {2})));User ID={3};Password={4}", strDBHostName, strDBPort, strDBName, strDBUser, strDBPassword)

        'GetConntionSource = ConfigurationManager.AppSettings("ConnectionString")

    End Function

    Private Shared ReadOnly connectionString As String = GetConntionSource()

    Private Shared ReadOnly connectionString77 As String = ConfigurationManager.AppSettings("MesReportOracleConn") '77报表数据库


#Region "公用方法"
    ''' <summary>
    ''' 查询某个表中某个字段的最大值
    ''' 查询的字段必须可以转换成整型
    ''' </summary>
    ''' <param name="FieldName">字段名</param>
    ''' <param name="TableName">表名</param>
    ''' <returns></returns>
    Public Shared Function GetMaxID(ByVal FieldName As String, ByVal TableName As String) As Integer
        Dim strsql As String = "select max(" + FieldName + ")+1 from " + TableName
        Dim obj As Object = GetSingle(strsql)
        If obj Is Nothing Then
            Return 1
        Else
            Return Integer.Parse(obj.ToString())
        End If
    End Function
    ''' <summary>
    ''' 查询某些数据是否存在
    ''' </summary>
    ''' <param name="strSql">查询语句</param>
    ''' <param name="cmdParms">参数集合</param>
    ''' <returns></returns>
    Public Shared Function Exists(ByVal strSql As String, ByVal ParamArray cmdParms As OracleParameter()) As Boolean
        Dim obj As Object = GetSingle(strSql, cmdParms)
        Dim cmdresult As Integer
        If ([Object].Equals(obj, Nothing)) OrElse ([Object].Equals(obj, System.DBNull.Value)) Then
            cmdresult = 0
        Else
            cmdresult = Integer.Parse(obj.ToString())
        End If
        If cmdresult = 0 Then
            Return False
        Else
            Return True
        End If
    End Function

    ''' <summary>
    ''' 判断表中字段的值是否存在
    ''' create:Wangjh
    ''' </summary>
    ''' <param name="tableName"></param>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function IsExistInTable(ByVal tableName As String, ByVal para As Dictionary(Of String, String)) As Boolean
        Dim strSql As New StringBuilder
        Dim strSelect As String = ""
        Dim strCondition As New StringBuilder
        strSql.AppendLine("SELECT ")
        For Each strkey As String In para.Keys
            strSelect = strSelect & "t." & strkey & ","
            'strCondition = strCondition & "t." & strkey & "=" & para(strkey) & "AND "
            'String.Format(strCondition = strCondition & "t." & strkey & "= '{0}' AND ", para(strkey))
            strCondition.AppendFormat("t." & strkey & "= '{0}' AND ", para(strkey))
        Next
        strSelect = strSelect.Substring(0, strSelect.Length - 1)
        Dim strConditionData = (strCondition.ToString).Substring(0, strCondition.Length - 4)
        strSql.AppendLine(strSelect & " FROM " & tableName & " t WHERE " & strConditionData)
        Dim obj As Object = GetSingle(strSql.ToString)
        If ([Object].Equals(obj, Nothing)) OrElse ([Object].Equals(obj, System.DBNull.Value)) Then
            IsExistInTable = False
            Exit Function
        End If
        strSql.Clear()

        IsExistInTable = True
    End Function
    ''' <summary>
    ''' 获取批次当前信息分页数据
    ''' Create:YangSJ
    ''' </summary>
    ''' <param name="oPageDTO"></param>
    ''' <param name="strSQL"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetContainerForPage(ByVal oPageDTO As uMESPagingDataDTO, ByVal strSQL As String) As uMESProcessResult
        Dim result As uMESProcessResult = RunProcedureForPage(oPageDTO)
        Dim sb As New StringBuilder
        sb.AppendLine(strSQL)

        Dim containerflag As String = ""
        Dim dv As DataView = result.DBTable.DefaultView
        dv.RowFilter = "CONTAINERTYPE='7'"
        For Each drv As DataRowView In dv
            containerflag = String.Format("{0} ( C.TOGETHERFLAG='{1}' AND C.PRODUCTID='{2}' AND C.WORKFLOWSTEPID='{3}'  ",
                                          containerflag, drv("TOGETHERFLAG"), drv("PRODUCTID"), drv("WORKFLOWSTEPID"))
            If result.DBTable.Columns.Contains("NOWWORKFLOWSTEPID") Then 'add by tianFW 2018年6月1日 报工当前序添加查询 报工页面使用
                containerflag = String.Format("{0} AND C.NOWWORKFLOWSTEPID='{1}'", containerflag, drv("NOWWORKFLOWSTEPID"))
            End If
            If IsDBNull(drv("MFGORDERNAME")) Then
                containerflag = String.Format("{0} AND C.MFGORDERNAME IS NULL) OR", containerflag)
            Else
                containerflag = String.Format("{0} AND C.MFGORDERNAME='{1}') OR", containerflag, drv("MFGORDERNAME"))
            End If

        Next

        sb.AppendLine(" AND C.CONTAINERTYPE='7' ")
        Dim containerDT As New DataTable


        If containerflag.Length > 1 Then
            containerflag = containerflag.Substring(0, containerflag.Length - 2)
            sb.AppendFormat(" AND ({0})", containerflag)
            sb.AppendLine("  ORDER BY C.CONTAINERNAME") 'add:Wangjh 20170920
            containerDT = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Else
            containerDT = result.DBTable.Clone
        End If
        containerDT.Columns.Add("FaContianerID")

        Dim ds As New DataSet
        ds.Tables.Add(result.DBTable)
        ds.Tables(0).TableName = "sumContainer"
        ds.Tables.Add(containerDT)
        ds.Tables(1).TableName = "sonContainer"
        If ds.Tables(0).Columns.Contains("NOWWORKFLOWSTEPID") Then 'add by tianFW 2018年6月1日 报工当前序添加查询 只有这一个
            ds.Relations.Add("SumContainer",
           New DataColumn() {ds.Tables(0).Columns("FaContianerID"), ds.Tables(0).Columns("MFGORDERNAME"), ds.Tables(0).Columns("PRODUCTID"), ds.Tables(0).Columns("TOGETHERFLAG"),
                           ds.Tables(0).Columns("CONTAINERTYPE"), ds.Tables(0).Columns("WORKFLOWSTEPID"), ds.Tables(0).Columns("FACTORYID"), ds.Tables(0).Columns("NOWWORKFLOWSTEPID")},
           New DataColumn() {ds.Tables(1).Columns("FaContianerID"), ds.Tables(1).Columns("MFGORDERNAME"), ds.Tables(1).Columns("PRODUCTID"), ds.Tables(1).Columns("TOGETHERFLAG"),
                        ds.Tables(1).Columns("CONTAINERTYPE"), ds.Tables(1).Columns("WORKFLOWSTEPID"), ds.Tables(1).Columns("FACTORYID"), ds.Tables(1).Columns("NOWWORKFLOWSTEPID")})
        ElseIf ds.Tables(0).Columns.Contains("WorkcenterName") And ds.Tables(0).Columns.Contains("WorkcenterID") Then

            If ds.Tables(0).Columns.Contains("PLANNEDSTARTDATE") And ds.Tables(0).Columns.Contains("PLANNEDCOMPLETIONDATE") Then 'add:Wangjh 20220530
                ds.Relations.Add("SumContainer",
              New DataColumn() {ds.Tables(0).Columns("FaContianerID"), ds.Tables(0).Columns("MFGORDERNAME"), ds.Tables(0).Columns("PRODUCTID"), ds.Tables(0).Columns("TOGETHERFLAG"),
                                ds.Tables(0).Columns("CONTAINERTYPE"), ds.Tables(0).Columns("WORKFLOWSTEPID"), ds.Tables(0).Columns("FACTORYID"), ds.Tables(0).Columns("WorkcenterName"), ds.Tables(0).Columns("WorkcenterID"),
                                ds.Tables(0).Columns("PLANNEDSTARTDATE"), ds.Tables(0).Columns("PLANNEDCOMPLETIONDATE")},
              New DataColumn() {ds.Tables(1).Columns("FaContianerID"), ds.Tables(1).Columns("MFGORDERNAME"), ds.Tables(1).Columns("PRODUCTID"), ds.Tables(1).Columns("TOGETHERFLAG"),
                                ds.Tables(1).Columns("CONTAINERTYPE"), ds.Tables(1).Columns("WORKFLOWSTEPID"), ds.Tables(1).Columns("FACTORYID"), ds.Tables(1).Columns("WorkcenterName"), ds.Tables(1).Columns("WorkcenterID"),
                                ds.Tables(1).Columns("PLANNEDSTARTDATE"), ds.Tables(1).Columns("PLANNEDCOMPLETIONDATE")})

            Else
                ds.Relations.Add("SumContainer",
               New DataColumn() {ds.Tables(0).Columns("FaContianerID"), ds.Tables(0).Columns("MFGORDERNAME"), ds.Tables(0).Columns("PRODUCTID"), ds.Tables(0).Columns("TOGETHERFLAG"),
                                 ds.Tables(0).Columns("CONTAINERTYPE"), ds.Tables(0).Columns("WORKFLOWSTEPID"), ds.Tables(0).Columns("FACTORYID"), ds.Tables(0).Columns("WorkcenterName"), ds.Tables(0).Columns("WorkcenterID")},
               New DataColumn() {ds.Tables(1).Columns("FaContianerID"), ds.Tables(1).Columns("MFGORDERNAME"), ds.Tables(1).Columns("PRODUCTID"), ds.Tables(1).Columns("TOGETHERFLAG"),
                                 ds.Tables(1).Columns("CONTAINERTYPE"), ds.Tables(1).Columns("WORKFLOWSTEPID"), ds.Tables(1).Columns("FACTORYID"), ds.Tables(1).Columns("WorkcenterName"), ds.Tables(1).Columns("WorkcenterID")})
            End If

          ElseIf ds.Tables(0).Columns.Contains("WorkcenterName") Then
            ds.Relations.Add("SumContainer",
              New DataColumn() {ds.Tables(0).Columns("FaContianerID"), ds.Tables(0).Columns("MFGORDERNAME"), ds.Tables(0).Columns("PRODUCTID"), ds.Tables(0).Columns("TOGETHERFLAG"),
                                ds.Tables(0).Columns("CONTAINERTYPE"), ds.Tables(0).Columns("WORKFLOWSTEPID"), ds.Tables(0).Columns("FACTORYID"), ds.Tables(0).Columns("WorkcenterName")},
              New DataColumn() {ds.Tables(1).Columns("FaContianerID"), ds.Tables(1).Columns("MFGORDERNAME"), ds.Tables(1).Columns("PRODUCTID"), ds.Tables(1).Columns("TOGETHERFLAG"),
                                ds.Tables(1).Columns("CONTAINERTYPE"), ds.Tables(1).Columns("WORKFLOWSTEPID"), ds.Tables(1).Columns("FACTORYID"), ds.Tables(1).Columns("WorkcenterName")})
        ElseIf ds.Tables(0).Columns.Contains("WorkcenterID") Then
            ds.Relations.Add("SumContainer",
            New DataColumn() {ds.Tables(0).Columns("FaContianerID"), ds.Tables(0).Columns("MFGORDERNAME"), ds.Tables(0).Columns("PRODUCTID"), ds.Tables(0).Columns("TOGETHERFLAG"),
                            ds.Tables(0).Columns("CONTAINERTYPE"), ds.Tables(0).Columns("WORKFLOWSTEPID"), ds.Tables(0).Columns("FACTORYID"), ds.Tables(0).Columns("WorkcenterID")},
            New DataColumn() {ds.Tables(1).Columns("FaContianerID"), ds.Tables(1).Columns("MFGORDERNAME"), ds.Tables(1).Columns("PRODUCTID"), ds.Tables(1).Columns("TOGETHERFLAG"),
                         ds.Tables(1).Columns("CONTAINERTYPE"), ds.Tables(1).Columns("WORKFLOWSTEPID"), ds.Tables(1).Columns("FACTORYID"), ds.Tables(1).Columns("WorkcenterID")})
        Else
            ds.Relations.Add("SumContainer",
           New DataColumn() {ds.Tables(0).Columns("FaContianerID"), ds.Tables(0).Columns("MFGORDERNAME"), ds.Tables(0).Columns("PRODUCTID"), ds.Tables(0).Columns("TOGETHERFLAG"),
                           ds.Tables(0).Columns("CONTAINERTYPE"), ds.Tables(0).Columns("WORKFLOWSTEPID"), ds.Tables(0).Columns("FACTORYID")},
           New DataColumn() {ds.Tables(1).Columns("FaContianerID"), ds.Tables(1).Columns("MFGORDERNAME"), ds.Tables(1).Columns("PRODUCTID"), ds.Tables(1).Columns("TOGETHERFLAG"),
                        ds.Tables(1).Columns("CONTAINERTYPE"), ds.Tables(1).Columns("WORKFLOWSTEPID"), ds.Tables(1).Columns("FACTORYID")})
        End If

        result.DBDataSet = ds
        GetContainerForPage = result
    End Function

    ''' <summary>
    ''' 主表数据赋值
    ''' </summary>
    ''' <param name="row"></param>
    ''' <param name="srcRow"></param>
    ''' <param name="dictField"></param>
    ''' <param name="columns"></param>
    ''' <remarks></remarks>
    Private Shared Sub UpdateRowData(ByRef row As DataRow, ByVal srcRow As DataRow, ByVal dictField As Dictionary(Of String, String), ByVal columns As DataColumnCollection)
        row("FaContianerID") = srcRow.Item("CONTAINERID")
        row("CONTAINERID") = srcRow.Item("CONTAINERID")
        row("CONTAINERNAME") = srcRow.Item("CONTAINERNAME")
        row("WIPQTY") = srcRow.Item("QTY")
        If Not srcRow.IsNull("WORKREPORTNUM") Then
            row("WORKREPORTNUM") = srcRow.Item("WORKREPORTNUM")
        End If
        If Not IsNothing(dictField) Then
            '为追加列赋值
            For Each strField As String In dictField.Keys
                If Not columns.Contains(strField) Then
                    Continue For
                End If
                If Not srcRow.IsNull(strField) Then
                    row(strField) = srcRow.Item(strField)
                End If
            Next
        End If
    End Sub

    ''' <summary>
    ''' 新建OracleParameter参数
    ''' </summary>
    ''' <param name="strName"></param>
    ''' <param name="strValue"></param>
    ''' <param name="oracleType"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function NewParameter(ByVal strName As String, ByVal strValue As Object, ByVal oracleType As System.Data.OracleClient.OracleType) As OracleParameter

        NewParameter = New OracleParameter(strName, oracleType)
        NewParameter.Direction = ParameterDirection.Input
        NewParameter.Value = strValue
    End Function

#End Region

#Region "执行简单SQL语句"
    ''' <summary>
    ''' 执行多条SQL语句，实现数据库事务。
    ''' </summary>
    ''' <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的OracleParameter[]）</param>
    Public Shared Sub ExecuteSqlTranByHash(ByVal SQLStringList As Dictionary(Of String, OracleParameter()))
        Using conn As New OracleConnection(connectionString)
            conn.Open()
            Using trans As OracleTransaction = conn.BeginTransaction()
                Dim cmd As New OracleCommand()
                Try
                    '循环
                    For Each myDE In SQLStringList
                        Dim cmdText As String = myDE.Key.ToString()
                        '获得键值
                        Dim cmdParms As OracleParameter() = DirectCast(myDE.Value, OracleParameter())
                        '通过键值获得值
                        PrepareCommand(cmd, conn, trans, cmdText, cmdParms)
                        Dim val As Integer = cmd.ExecuteNonQuery()
                        cmd.Parameters.Clear()
                    Next
                    '事务提交
                    trans.Commit()
                Catch
                    trans.Rollback()
                    Throw
                End Try
            End Using
        End Using
    End Sub
    ''' <summary>
    ''' 执行SQL语句，返回影响的记录数
    ''' 例如数据添加、删除、更新操作
    ''' </summary>
    ''' <param name="SQLString">SQL语句</param>
    ''' <returns>影响的记录数</returns>
    Public Shared Function ExecuteSql(ByVal SQLString As String) As Integer
        Using connection As New OracleConnection(connectionString)
            Using cmd As New OracleCommand(SQLString, connection)
                Try
                    connection.Open()
                    Dim rows As Integer = cmd.ExecuteNonQuery()
                    Return rows
                Catch E As OracleException
                    connection.Close()
                    Throw New Exception(E.Message)
                End Try
            End Using
        End Using
    End Function

    ''' <summary>
    ''' 执行多条SQL语句，实现数据库事务。
    ''' 当所有的执行语句全部执行成功，没有异常后才能进行事务的提交
    ''' </summary>
    ''' <param name="SQLStringList">多条SQL语句</param>		
    Public Shared Sub ExecuteSqlTran(ByVal SQLStringList As ArrayList)
        Using conn As New OracleConnection(connectionString)
            conn.Open()
            Dim cmd As New OracleCommand()
            cmd.Connection = conn
            Dim tx As OracleTransaction = conn.BeginTransaction()
            cmd.Transaction = tx
            Try
                For n As Integer = 0 To SQLStringList.Count - 1
                    Dim strsql As String = SQLStringList(n).ToString()
                    If strsql.Trim().Length > 1 Then
                        cmd.CommandText = strsql
                        cmd.ExecuteNonQuery()
                    End If
                Next
                '事务提交
                tx.Commit()
            Catch E As OracleException
                tx.Rollback()
                '事务回滚
                Throw New Exception(E.Message)
            End Try
        End Using
    End Sub

    ''' <summary>
    ''' 执行多条SQL语句，实现数据库事务。
    ''' </summary>
    ''' <param name="SQLStringList"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ExecuteSqlListTran(ByVal SQLStringList As ArrayList) As Boolean
        ExecuteSqlListTran = False
        Using conn As New OracleConnection(connectionString)
            conn.Open()
            Dim cmd As New OracleCommand()
            cmd.Connection = conn
            Dim tx As OracleTransaction = conn.BeginTransaction()
            cmd.Transaction = tx
            Try
                For n As Integer = 0 To SQLStringList.Count - 1
                    Dim strsql As String = SQLStringList(n).ToString()
                    If strsql.Trim().Length > 1 Then
                        cmd.CommandText = strsql
                        cmd.ExecuteNonQuery()
                    End If
                Next
                '事务提交
                tx.Commit()
                Return True
            Catch E As OracleException
                tx.Rollback()
                '事务回滚
                Throw New Exception(E.Message)
            End Try
        End Using
    End Function

    ''' <summary>
    ''' 执行带一个存储过程参数的的SQL语句。
    ''' </summary>
    ''' <param name="SQLString">SQL语句/或者是存储过程</param>
    ''' <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
    ''' <returns>影响的记录数</returns>
    Public Shared Function ExecuteSql(ByVal SQLString As String, ByVal content As String) As Integer
        Using connection As New OracleConnection(connectionString)
            Dim cmd As New OracleCommand(SQLString, connection)
            Dim myParameter As New OracleParameter("content", OracleType.Blob)
            myParameter.Value = content
            cmd.Parameters.Add(myParameter)
            Try
                connection.Open()
                Dim rows As Integer = cmd.ExecuteNonQuery()
                Return rows
            Catch E As OracleException
                Throw New Exception(E.Message)
            Finally
                cmd.Dispose()
                connection.Close()
            End Try
        End Using
    End Function

    Public Shared Function ExecuteSql(ByVal SQLString As String, ByVal ConnStr As String, ByVal ParamArray cmdParms As OracleParameter()) As Integer
        Using connection As New OracleConnection(ConnStr)
            Using cmd As New OracleCommand()
                Try
                    PrepareCommand(cmd, connection, Nothing, SQLString, cmdParms)
                    Dim rows As Integer = cmd.ExecuteNonQuery()
                    cmd.Parameters.Clear()
                    Return rows
                Catch E As OracleException
                    Throw New Exception(E.Message)
                End Try
            End Using
        End Using
    End Function

    ''' <summary>
    ''' 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
    ''' </summary>
    ''' <param name="strSQL">SQL语句</param>
    ''' <param name="fs">图像字节,数据库的字段类型为image的情况</param>
    ''' <returns>影响的记录数</returns>
    Public Shared Function ExecuteSqlInsertImg(ByVal strSQL As String, ByVal fs As Byte()) As Integer
        Using connection As New OracleConnection(connectionString)
            Dim cmd As New OracleCommand(strSQL, connection)
            Dim myParameter As New OracleParameter("fs", OracleType.Blob)
            myParameter.Value = fs
            cmd.Parameters.Add(myParameter)
            Try
                connection.Open()
                Dim rows As Integer = cmd.ExecuteNonQuery()
                Return rows
            Catch E As OracleException
                Throw New Exception(E.Message)
            Finally
                cmd.Dispose()
                connection.Close()
            End Try
        End Using
    End Function
    ''' <summary>
    ''' 执行一条计算查询结果语句，返回查询结果（object）。
    ''' </summary>
    ''' <param name="SQLString">计算查询结果语句</param>
    ''' <returns>查询结果（object）</returns>
    Public Shared Function GetSingle(ByVal SQLString As String) As Object
        Using connection As New OracleConnection(connectionString)
            Using cmd As New OracleCommand(SQLString, connection)
                Try
                    connection.Open()
                    Dim obj As Object = cmd.ExecuteScalar()
                    '统计功能（一行一列）
                    If ([Object].Equals(obj, Nothing)) OrElse ([Object].Equals(obj, System.DBNull.Value)) Then
                        Return Nothing
                    Else
                        Return obj
                    End If
                Catch e As OracleException
                    connection.Close()
                    Throw New Exception(e.Message)
                End Try
            End Using
        End Using
    End Function
    ''' <summary>
    ''' 执行查询语句，返回OracleDataReader
    ''' </summary>
    ''' <param name="strSQL">查询语句</param>
    ''' <returns>OracleDataReader</returns>
    Public Shared Function ExecuteReader(ByVal strSQL As String) As OracleDataReader
        Dim connection As New OracleConnection(connectionString)
        Dim cmd As New OracleCommand(strSQL, connection)
        Try
            connection.Open()
            Dim myReader As OracleDataReader = cmd.ExecuteReader()
            Return myReader
        Catch e As OracleException
            Throw New Exception(e.Message)
        End Try
    End Function
    ''' <summary>
    ''' 执行查询语句，返回DataSet
    ''' </summary>
    ''' <param name="SQLString">查询语句</param>
    ''' <returns>DataSet</returns>
    Public Shared Function Query_ReturnDataSet(ByVal SQLString As String) As DataSet
        Using connection As New OracleConnection(connectionString)
            Dim ds As New DataSet()
            Try
                connection.Open()
                Dim command As New OracleDataAdapter(SQLString, connection)
                command.Fill(ds, "ds")
            Catch ex As OracleException
                Throw New Exception(ex.Message)
            End Try
            Return ds
        End Using
    End Function

    ''' <summary>
    ''' 执行查询语句，返回DataTable
    ''' </summary>
    ''' <param name="SQLString">查询语句</param>
    ''' <returns>DataSet</returns>
    Public Shared Function Query_ReturnDatTable(ByVal SQLString As String) As DataTable
        Using connection As New OracleConnection(connectionString)
            Dim dt As New DataTable
            Try
                connection.Open()
                Dim command As New OracleDataAdapter(SQLString, connection)
                command.Fill(dt)
            Catch ex As OracleException
                Throw New Exception(ex.Message)
            End Try
            Return dt
        End Using
    End Function

    Public Shared Function QueryByConn(ByVal SQLString As String, ByVal strConn As String) As DataSet
        Using connection As New OracleConnection(strConn)
            Dim ds As New DataSet()
            Try
                connection.Open()
                Dim command As New OracleDataAdapter(SQLString, connection)
                command.Fill(ds, "ds")
            Catch ex As OracleException
                Throw New Exception(ex.Message)
            End Try
            Return ds
        End Using
    End Function
#End Region

#Region "执行带参数的SQL语句"
    ''' <summary>
    ''' 执行SQL语句，返回影响的记录数
    ''' </summary>
    ''' <param name="SQLString">SQL语句</param>
    ''' <returns>影响的记录数</returns>
    Public Shared Function ExecuteSql(ByVal SQLString As String, ByVal ParamArray cmdParms As OracleParameter()) As Integer
        Using connection As New OracleConnection(connectionString)
            Using cmd As New OracleCommand()
                Try
                    PrepareCommand(cmd, connection, Nothing, SQLString, cmdParms)
                    Dim rows As Integer = cmd.ExecuteNonQuery()
                    cmd.Parameters.Clear()
                    Return rows
                Catch E As OracleException
                    Throw New Exception(E.Message)
                End Try
            End Using
        End Using
    End Function
    ''' <summary>
    ''' 执行多条SQL语句，实现数据库事务。
    ''' </summary>
    ''' <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的OracleParameter[]）</param>
    Public Shared Sub ExecuteSqlTran(ByVal SQLStringList As Hashtable)
        Using conn As New OracleConnection(connectionString)
            conn.Open()
            Using trans As OracleTransaction = conn.BeginTransaction()
                Dim cmd As New OracleCommand()
                Try
                    '循环
                    For Each myDE As DictionaryEntry In SQLStringList
                        Dim cmdText As String = myDE.Key.ToString()
                        '获得键值
                        Dim cmdParms As OracleParameter() = DirectCast(myDE.Value, OracleParameter())
                        '通过键值获得值
                        PrepareCommand(cmd, conn, trans, cmdText, cmdParms)
                        Dim val As Integer = cmd.ExecuteNonQuery()
                        cmd.Parameters.Clear()
                        '事务提交
                        'trans.Commit()
                    Next
                    '事务提交
                    trans.Commit()
                Catch
                    trans.Rollback()
                    Throw
                End Try
            End Using
        End Using
    End Sub

    ''' <summary>
    ''' 执行多条SQL语句，实现数据库事务。
    ''' </summary>
    ''' <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的OracleParameter[]）</param>
    Public Shared Function ExecuteSqlTran(ByVal SQLStringList As List(Of Object())) As Boolean
        ExecuteSqlTran = False
        Using conn As New OracleConnection(connectionString)
            conn.Open()
            Using trans As OracleTransaction = conn.BeginTransaction()
                Dim cmd As New OracleCommand()
                Try
                    '循环
                    For Each myDE As Object() In SQLStringList
                        If myDE.Length = 0 Then
                            Continue For
                        End If
                        Dim cmdText As String = String.Empty
                        If myDE.Length > 0 Then
                            cmdText = myDE.First.ToString()
                        End If
                        '获得键值
                        Dim cmdParms As OracleParameter() = Nothing
                        If myDE.Length = 2 Then
                            cmdParms = DirectCast(myDE.Last, OracleParameter())
                        End If

                        '通过键值获得值
                        PrepareCommand(cmd, conn, trans, cmdText, cmdParms)
                        Dim val As Integer = cmd.ExecuteNonQuery()
                        cmd.Parameters.Clear()
                    Next
                    '事务提交
                    trans.Commit()
                    ExecuteSqlTran = True

                Catch
                    trans.Rollback()
                    Throw
                End Try
            End Using
        End Using
    End Function

    ''' <summary>
    ''' 执行一条计算查询结果语句，返回查询结果（object）。
    ''' </summary>
    ''' <param name="SQLString">计算查询结果语句</param>
    ''' <returns>查询结果（object）</returns>
    Public Shared Function GetSingle(ByVal SQLString As String, ByVal ParamArray cmdParms As OracleParameter()) As Object
        Using connection As New OracleConnection(connectionString)
            Using cmd As New OracleCommand()
                Try
                    PrepareCommand(cmd, connection, Nothing, SQLString, cmdParms)
                    Dim obj As Object = cmd.ExecuteScalar()
                    cmd.Parameters.Clear()
                    If ([Object].Equals(obj, Nothing)) OrElse ([Object].Equals(obj, System.DBNull.Value)) Then
                        Return Nothing
                    Else
                        Return obj
                    End If
                Catch e As OracleException
                    Throw New Exception(e.Message)
                End Try
            End Using
        End Using
    End Function
    ''' <summary>
    ''' 执行查询语句，返回OracleDataReader
    ''' </summary>
    ''' <param name="strSQL">查询语句</param>
    ''' <returns>OracleDataReader</returns>
    Public Shared Function ExecuteReader(ByVal SQLString As String, ByVal ParamArray cmdParms As OracleParameter()) As OracleDataReader
        Dim connection As New OracleConnection(connectionString)
        Dim cmd As New OracleCommand()
        Try
            PrepareCommand(cmd, connection, Nothing, SQLString, cmdParms)
            Dim myReader As OracleDataReader = cmd.ExecuteReader()
            cmd.Parameters.Clear()
            Return myReader
        Catch e As OracleException
            Throw New Exception(e.Message)
        End Try
    End Function
    ''' <summary>
    ''' 执行查询语句，返回DataSet
    ''' </summary>
    ''' <param name="SQLString">查询语句</param>
    ''' <returns>DataSet</returns>
    Public Shared Function Query(ByVal SQLString As String, ByVal ParamArray cmdParms As OracleParameter()) As DataSet
        Using connection As New OracleConnection(connectionString)
            Dim cmd As New OracleCommand()
            PrepareCommand(cmd, connection, Nothing, SQLString, cmdParms)
            Using da As New OracleDataAdapter(cmd)
                Dim ds As New DataSet()
                Try
                    da.Fill(ds, "ds")
                    cmd.Parameters.Clear()
                Catch ex As OracleException
                    Throw New Exception(ex.Message)
                End Try
                Return ds
            End Using
        End Using
    End Function
    ''' <summary>
    '''在存在事务的情况下，新建数据库连接，执行sql语句，此处是关键，上面的所有功能操作都沿此而进行
    ''' 此处的sql语句可能是增加、删除、更新、查询等等操作
    ''' Prepare a command for execution
    ''' </summary>
    ''' <param name="cmd">OracleCommand对象</param>
    ''' <param name="conn">OracleConnection对象</param>
    ''' <param name="trans">OracleTransaction对象</param>
    ''' <param name="cmdType">Cmd type e.g. 存储过程或者sql语句</param>
    ''' <param name="cmdText">Command text, e.g. Select * from Products</param>
    ''' <param name="cmdParms">OracleParameters to use in the command</param>
    Private Shared Sub PrepareCommand(ByVal cmd As OracleCommand, ByVal conn As OracleConnection, ByVal trans As OracleTransaction, ByVal cmdText As String, ByVal cmdParms As OracleParameter())
        If conn.State <> ConnectionState.Open Then
            conn.Open()
        End If
        cmd.Connection = conn
        cmd.CommandText = cmdText
        If trans IsNot Nothing Then
            cmd.Transaction = trans
        End If
        cmd.CommandType = CommandType.Text
        'cmdType;
        If cmdParms IsNot Nothing Then
            For Each parm As OracleParameter In cmdParms
                cmd.Parameters.Add(parm)
            Next
        End If
    End Sub
#End Region

#Region "存储过程操作"
    ''' <summary>
    ''' 执行存储过程
    ''' </summary>
    ''' <param name="storedProcName">存储过程名</param>
    ''' <param name="parameters">存储过程参数</param>
    ''' <returns>OracleDataReader</returns>
    Public Shared Function RunProcedure(ByVal storedProcName As String, ByVal parameters As IDataParameter()) As OracleDataReader
        Dim connection As New OracleConnection(connectionString)
        Dim returnReader As OracleDataReader
        connection.Open()
        Dim command As OracleCommand = BuildQueryCommand(connection, storedProcName, parameters)
        command.CommandType = CommandType.StoredProcedure
        returnReader = command.ExecuteReader()
        Return returnReader
    End Function
    ''' <summary>
    ''' 执行存储过程
    ''' </summary>
    ''' <param name="storedProcName">存储过程名</param>
    ''' <param name="parameters">存储过程参数</param>
    ''' <param name="tableName">DataSet结果中的表名</param>
    ''' <returns>DataSet</returns>
    Public Shared Function RunProcedure(ByVal storedProcName As String, ByVal parameters As IDataParameter(), ByVal tableName As String) As DataSet
        Try
            Using connection As New OracleConnection(connectionString)
                Dim dataSet As New DataSet()
                connection.Open()
                Dim sqlDA As New OracleDataAdapter()
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters)
                sqlDA.Fill(dataSet, tableName)
                connection.Close()
                'trans.Dispose();
                Return dataSet
            End Using
        Catch RunProcedureEx As Exception
            Throw RunProcedureEx
        End Try
    End Function
    ''' <summary>
    ''' 构建 OracleCommand 对象(用来返回一个结果集，而不是一个整数值)
    ''' </summary>
    ''' <param name="connection">数据库连接</param>
    ''' <param name="storedProcName">存储过程名</param>
    ''' <param name="parameters">存储过程参数</param>
    ''' <returns>OracleCommand</returns>
    Private Shared Function BuildQueryCommand(ByVal connection As OracleConnection, ByVal storedProcName As String, ByVal parameters As IDataParameter()) As OracleCommand
        Dim command As New OracleCommand(storedProcName, connection)
        command.CommandType = CommandType.StoredProcedure
        For Each parameter As OracleParameter In parameters
            command.Parameters.Add(parameter)
        Next
        Return command
    End Function
    ''' <summary>
    ''' 执行存储过程，返回影响的行数		
    ''' </summary>
    ''' <param name="storedProcName">存储过程名</param>
    ''' <param name="parameters">存储过程参数</param>
    ''' <param name="rowsAffected">影响的行数</param>
    ''' <returns></returns>
    Public Shared Function RunProcedure(ByVal storedProcName As String, ByVal parameters As IDataParameter(), ByRef rowsAffected As Integer) As Integer
        Dim result As Integer = 0
        Try
            Using connection As New OracleConnection(connectionString)
                connection.Open()
                Dim command As OracleCommand = BuildIntCommand(connection, storedProcName, parameters)
                Dim trans As OracleTransaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)
                If trans IsNot Nothing Then
                    command.Transaction = trans
                    result = command.ExecuteNonQuery()
                    trans.Commit()
                End If
            End Using
        Catch RunProcedureEx As Exception
            Throw RunProcedureEx
        End Try
        rowsAffected = result
        Return result
    End Function
    ''' <summary>
    ''' 执行存储过程，查询，并且有输出参数	
    ''' </summary>
    ''' <param name="storedProcName">存储过程名</param>
    ''' <param name="parameters">存储过程参数</param>
    ''' <param name="rowsAffected">影响的行数</param>
    ''' <returns></returns>
    Public Shared Function RunProcedureQuery(ByVal storedProcName As String, ByVal parameters As IDataParameter(), ByRef rowsAffected As Integer) As String
        Dim outString As String = ""
        Try
            Using connection As New OracleConnection(connectionString)
                connection.Open()
                Dim command As OracleCommand = BuildIntCommand(connection, storedProcName, parameters)
                Dim trans As OracleTransaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)
                If trans IsNot Nothing Then
                    command.Transaction = trans
                    command.ExecuteNonQuery()
                    trans.Commit()
                End If
                '输出参数
                outString = command.Parameters("TempID").Value.ToString()
            End Using
        Catch RunProcedureEx As Exception
            Throw RunProcedureEx
        End Try
        rowsAffected = 0
        Return outString
    End Function
    ''' <summary>
    ''' 创建 OracleCommand 对象实例(用来返回一个整数值)	
    ''' </summary>
    ''' <param name="storedProcName">存储过程名</param>
    ''' <param name="parameters">存储过程参数</param>
    ''' <returns>OracleCommand 对象实例</returns>
    Private Shared Function BuildIntCommand(ByVal connection As OracleConnection, ByVal storedProcName As String, ByVal parameters As IDataParameter()) As OracleCommand
        Dim command As OracleCommand = BuildQueryCommand(connection, storedProcName, parameters)
        Return command
    End Function
    ''' <summary>
    ''' 执行存储过程，没有返回任何信息，如插入数据等
    ''' </summary>
    ''' <param name="procname"></param>
    ''' <param name="cmdParms"></param>
    Public Shared Sub ExecuteProc(ByVal procname As String, ByVal ParamArray cmdParms As OracleParameter())
        Using SqlConn As New OracleConnection(connectionString)
            If SqlConn.State = ConnectionState.Closed Then
                SqlConn.Open()
            End If
            Dim sdar As New OracleDataAdapter()
            sdar.SelectCommand = New OracleCommand()
            sdar.SelectCommand.Connection = SqlConn
            sdar.SelectCommand.CommandText = procname
            sdar.SelectCommand.CommandType = CommandType.StoredProcedure
            If cmdParms IsNot Nothing Then
                For Each parm As OracleParameter In cmdParms
                    sdar.SelectCommand.Parameters.Add(parm)
                Next
            End If
            Dim i As Integer = sdar.SelectCommand.ExecuteNonQuery()
            sdar.Dispose()
        End Using
    End Sub
    ''' <summary>
    ''' 执行参数化的Sql语句,返回DataTable
    ''' </summary>
    ''' <param name="p_ProcedureName">sql语句</param>
    ''' <param name="p_OracleParameterArray">Parm数组</param>
    ''' <returns>返回DataTable</returns>
    Public Shared Function ExeParameDataTable(ByVal p_ProcedureName As String, ByVal p_OracleParameterArray As OracleParameter()) As DataTable
        Try
            Using SqlConn As New OracleConnection(connectionString)
                If SqlConn.State = ConnectionState.Closed Then
                    SqlConn.Open()
                End If
                Dim dt As New DataTable()
                Dim da As New OracleDataAdapter(p_ProcedureName, SqlConn)
                For Each Sq As OracleParameter In p_OracleParameterArray
                    da.SelectCommand.Parameters.Add(Sq)
                Next
                da.Fill(dt)
                da.SelectCommand.Parameters.Clear()
                Return dt
            End Using
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    ''' <summary>
    ''' 将金额从小写转换成大写字母书写
    ''' </summary>
    ''' <param name="endMoney">要转化的金额数目</param>
    ''' <returns>返回金额大写书写</returns>
    Public Shared Function ReturnMaxMoney(ByVal endMoney As Double) As String
        Try
            Dim returnstring As String = ""
            Using SqlConn As New OracleConnection(connectionString)
                If SqlConn.State = ConnectionState.Closed Then
                    SqlConn.Open()
                End If
                Using comm As New OracleCommand("MoneytoChinese22", SqlConn)
                    comm.CommandType = CommandType.StoredProcedure
                    '输入参数
                    comm.Parameters.Add("ChangeMoney", OracleType.Number)
                    '输出参数
                    comm.Parameters.Add("ReturnDaXie", OracleType.VarChar, 50)
                    'comm.Parameters["ReturnDaXie"].Direction = ParameterDirection.ReturnValue;
                    comm.Parameters("ChangeMoney").Value = endMoney
                    comm.Parameters("ReturnDaXie").Direction = ParameterDirection.Output
                    comm.ExecuteNonQuery()
                    '执行存储过程
                    returnstring = comm.Parameters("ReturnDaXie").Value.ToString()
                End Using
            End Using
            Return returnstring
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    ''' <summary>
    ''' 批量执行Command对象列表
    ''' </summary>
    Public Shared Sub ExecCmdListTrans(ByVal list As List(Of OracleCommand))
        Using OraConn As New OracleConnection(connectionString)
            If OraConn.State = ConnectionState.Closed Then
                OraConn.Open()
            End If
            Dim trans As OracleTransaction = OraConn.BeginTransaction()
            Try
                For i As Integer = 0 To list.Count - 1
                    list(i).Connection = OraConn
                    list(i).Transaction = trans
                    list(i).ExecuteNonQuery()
                Next
                trans.Commit()
            Catch ex As Exception
                trans.Rollback()
                Throw ex
            End Try
        End Using
    End Sub

    ''' <summary>
    ''' 执行存储过程
    ''' </summary>
    ''' <param name="oPageDTO">翻页参数</param>
    ''' <returns>DataSet</returns>
    Public Shared Function RunProcedureForPage(ByVal oPageDTO As uMESPagingDataDTO) As uMESProcessResult

        ' tbPage.Text = curPage.ToString()
        'Dim oEntity As New uMESPagingDataDTO
        Dim oResult As New uMESProcessResult

        Try
            Dim oCon As New OracleConnection(connectionString)

            Dim oCmd = New OracleCommand("p_page.Pagination", oCon)

            oCmd.CommandType = CommandType.StoredProcedure

            '//分页索引。
            Dim pIndex = New OracleParameter("Pindex", OracleType.Number)
            pIndex.Direction = ParameterDirection.Input
            pIndex.Value = oPageDTO.CurrentPageIndex

            '//要查询的sql语句
            Dim pSql = New OracleParameter("Psql", OracleType.VarChar)
            pSql.Direction = ParameterDirection.Input
            pSql.Value = oPageDTO.strSQL

            '//页面显示的数据条数
            Dim pSize = New OracleParameter("Psize", OracleType.Number)
            pSize.Direction = ParameterDirection.Input
            pSize.Value = oPageDTO.PageSize

            '//返回的分页数
            Dim pCount = New OracleParameter("Pcount", OracleType.Number)
            pCount.Direction = ParameterDirection.Output

            '//返回的记录数
            Dim pRCount = New OracleParameter("Prcount", OracleType.Number)
            pRCount.Direction = ParameterDirection.Output

            '//当前页数据记录集
            Dim pDataTabe = New OracleParameter("v_cur", OracleType.Cursor)
            pDataTabe.Direction = ParameterDirection.Output

            '//添加存储过程的参数
            oCmd.Parameters.Add(pIndex)
            oCmd.Parameters.Add(pSql)
            oCmd.Parameters.Add(pSize)
            oCmd.Parameters.Add(pCount)
            oCmd.Parameters.Add(pRCount)
            oCmd.Parameters.Add(pDataTabe)

            Dim da = New OracleDataAdapter(oCmd)

            Dim dt = New DataTable() '保存记录集

            da.Fill(dt) '//填充数据数据集

            oResult.PageCount = pCount.Value.ToString() ';//获取分页数
            oResult.RowCount = pRCount.Value.ToString() ';//获取记录数
            oResult.DBTable = dt
            oResult.Result = True
            RunProcedureForPage = oResult
        Catch ex As Exception
            oResult.Message = ex.Message
            oResult.Result = False
            Return oResult
        End Try

    End Function

    ''' <summary>
    ''' 执行存储过程
    ''' </summary>
    ''' <param name="oPageDTO">翻页参数</param>
    ''' <returns>DataSet</returns>
    Public Shared Function RunProcedureForPageByLongSQL(ByVal oPageDTO As uMESPagingDataDTO) As uMESProcessResult

        ' tbPage.Text = curPage.ToString()
        'Dim oEntity As New uMESPagingDataDTO
        Dim oResult As New uMESProcessResult

        Try
            Dim oCon As New OracleConnection(connectionString)

            Dim oCmd = New OracleCommand("p_pageForLong.Pagination", oCon)

            oCmd.CommandType = CommandType.StoredProcedure

            '//分页索引。
            Dim pIndex = New OracleParameter("Pindex", OracleType.Number)
            pIndex.Direction = ParameterDirection.Input
            pIndex.Value = oPageDTO.CurrentPageIndex

            '//要查询的sql语句
            Dim PMainFieldSQL = New OracleParameter("PMainFieldSQL", OracleType.VarChar)
            PMainFieldSQL.Direction = ParameterDirection.Input
            PMainFieldSQL.Value = oPageDTO.strFieldSQL

            '//要查询的sql语句
            Dim PMainSQL = New OracleParameter("PMainSQL ", OracleType.VarChar)
            PMainSQL.Direction = ParameterDirection.Input
            PMainSQL.Value = oPageDTO.strSQL

            '//要查询的sql语句
            Dim PMainSearchSQL = New OracleParameter("PMainSearchSQL ", OracleType.VarChar)
            PMainSearchSQL.Direction = ParameterDirection.Input
            PMainSearchSQL.Value = oPageDTO.strSearchSQL


            '//页面显示的数据条数
            Dim pSize = New OracleParameter("Psize", OracleType.Number)
            pSize.Direction = ParameterDirection.Input
            pSize.Value = oPageDTO.PageSize

            '//返回的分页数
            Dim pCount = New OracleParameter("Pcount", OracleType.Number)
            pCount.Direction = ParameterDirection.Output

            '//返回的记录数
            Dim pRCount = New OracleParameter("Prcount", OracleType.Number)
            pRCount.Direction = ParameterDirection.Output

            '//当前页数据记录集
            Dim pDataTabe = New OracleParameter("v_cur", OracleType.Cursor)
            pDataTabe.Direction = ParameterDirection.Output

            '//添加存储过程的参数
            oCmd.Parameters.Add(pIndex)
            oCmd.Parameters.Add(PMainFieldSQL)
            oCmd.Parameters.Add(PMainSQL)
            oCmd.Parameters.Add(PMainSearchSQL)
            oCmd.Parameters.Add(pSize)
            oCmd.Parameters.Add(pCount)
            oCmd.Parameters.Add(pRCount)
            oCmd.Parameters.Add(pDataTabe)

            Dim da = New OracleDataAdapter(oCmd)

            Dim dt = New DataTable() '保存记录集

            da.Fill(dt) '//填充数据数据集

            oResult.PageCount = pCount.Value.ToString() ';//获取分页数
            oResult.RowCount = pRCount.Value.ToString() ';//获取记录数
            oResult.DBTable = dt
            oResult.Result = True
            RunProcedureForPageByLongSQL = oResult
        Catch ex As Exception
            oResult.Message = ex.Message
            oResult.Result = False
            Return oResult
        End Try

    End Function

    ''' <summary>
    ''' Add by Yuxj20180515
    ''' </summary>
    ''' <param name="spname"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ReturnObjectParameters(ByVal spname As String) As DataTable
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT arg.OBJECT_NAME,arg.ARGUMENT_NAME,arg.SEQUENCE,arg.DATA_TYPE,arg.DATA_LENGTH,arg.in_out ")
        sb.AppendFormat("FROM all_arguments arg  WHERE object_name='{0}' ORDER BY SEQUENCE ASC", spname.ToUpper)
        Return Query_ReturnDatTable(sb.ToString)
    End Function

    ''' <summary>
    ''' Add by Yuxj20180515
    ''' </summary>
    ''' <param name="spName"></param>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function RunOraProcedure(ByVal spName As String, ByVal para As Dictionary(Of String, String)) As String
        Dim connection As New OracleConnection(connectionString)
        Dim command As New OracleCommand
        command.Connection = connection

        Dim dt As DataTable = ReturnObjectParameters(spName)
        For Each dr In dt.Rows
            If dr("in_out").ToString.IndexOf("IN") > -1 Then
                If Not para.ContainsKey(dr("ARGUMENT_NAME").ToString) Then
                    RunOraProcedure = "SP有参数不存在"
                    Exit Function
                Else
                    If dr("DATA_TYPE") = "VARCHAR2" Then
                        command.Parameters.Add(dr("ARGUMENT_NAME").ToString, OracleType.VarChar, 50).Direction = ParameterDirection.Input
                        command.Parameters(dr("ARGUMENT_NAME").ToString).Value = para(dr("ARGUMENT_NAME").ToString)
                    End If

                    If dr("DATA_TYPE") = "NUMBER" Then
                        command.Parameters.Add(dr("ARGUMENT_NAME").ToString, OracleType.UInt32, 50).Direction = ParameterDirection.Input
                        command.Parameters(dr("ARGUMENT_NAME").ToString).Value = para(dr("ARGUMENT_NAME").ToString)
                    End If

                    If dr("DATA_TYPE") = "DATE" Then
                        command.Parameters.Add(dr("ARGUMENT_NAME").ToString, OracleType.Timestamp).Direction = ParameterDirection.Input
                        command.Parameters(dr("ARGUMENT_NAME").ToString).Value = para(dr("ARGUMENT_NAME").ToString)
                    End If

                End If
            Else
                command.Parameters.Add(dr("ARGUMENT_NAME").ToString, OracleType.VarChar, 50).Direction = ParameterDirection.Output
            End If
        Next

        'command.Parameters.Add("V_MFGORDER", OracleType.VarChar, 50).Direction = ParameterDirection.Input
        'command.Parameters.Add("V_CONTAINERID", OracleType.VarChar, 50).Direction = ParameterDirection.Input
        'command.Parameters.Add("V_EMPLOYEEID", OracleType.VarChar, 50).Direction = ParameterDirection.Input
        'command.Parameters.Add("RES", OracleType.VarChar, 50).Direction = ParameterDirection.Output
        'command.Parameters("V_MFGORDER").Value = para("MfgOrderName")
        'command.Parameters("V_CONTAINERID").Value = para("ContainerId")
        'command.Parameters("V_EMPLOYEEID").Value = para("EmployeeId")

        connection.Open()
        command.CommandText = spName
        command.CommandType = CommandType.StoredProcedure
        command.ExecuteNonQuery()
        connection.Close()
        Return command.Parameters("Res").Value
    End Function

#End Region

#Region "重载-连接其他数据库"
    ''' <summary>
    ''' 执行查询语句(需指定数据库连接字符串)，返回DataTable -add zhunan 2017-12-4
    ''' </summary>
    ''' <param name="SQLString">查询语句</param>
    ''' <param name="strConnection">链接字符串</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function Query_ReturnDatTable(ByVal SQLString As String, ByVal strConnection As String) As DataTable
        Using connection As New OracleConnection(strConnection)
            Dim dt As New DataTable
            Try
                connection.Open()
                Dim command As New OracleDataAdapter(SQLString, connection)
                command.Fill(dt)
            Catch ex As OracleException
                Throw New Exception(ex.Message)
            End Try
            Return dt
        End Using
    End Function
#End Region


#Region "根据传入的实体类自动生成SQL并执行的添加、修改、删除通用方法  add by YangJiang  2018-03-14"
    ''' <summary>
    ''' 添加、修改、删除通用方法
    '''	create:YangJ
    ''' </summary>
    ''' <param name="objDTO">对象实体类</param>
    ''' <param name="optionType">操作类型</param>
    ''' <param name="primaryKey">主键标识列(修改/删除时的条件)</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ExcuteDTO(ByVal objDTO As Object, ByVal optionType As String, ByVal primaryKey As String) As Boolean
        Dim strsql As String
        Dim row As Integer
        Dim dic = GetDictionary(objDTO)
        Dim count = dic.Count
        Dim i As Integer = 0
        Select Case optionType
            Case "insert"
                strsql = CreateInsertSQl(objDTO)
                row = ExecuteSql(strsql, getPara(dic, count))
            Case "update"
                strsql = CreateUpdateSQL(objDTO, primaryKey)
                row = ExecuteSql(strsql, getPara(dic, count))
            Case "delete"
                strsql = CreateDeleteSQL(objDTO, primaryKey, dic(primaryKey))
                row = ExecuteSql(strsql)
        End Select
        If row > 0 Then
            Return True
        Else
            Return False
        End If
    End Function


    ''' <summary>
    ''' 生成添加SQL语句
    '''	create:YangJ
    ''' </summary>
    ''' <param name="obj">对象实体</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function CreateInsertSQl(ByVal obj As Object) As String
        Dim objName = obj.GetType.Name.ToString
        Dim qq = obj.GetType.GetProperties()
        Dim sbSQL As New StringBuilder
        Dim SQL As String = ""
        sbSQL.Append("INSERT INTO ")
        sbSQL.Append(GetTabName(objName) + "(")
        For Each t As System.Reflection.PropertyInfo In obj.GetType.GetProperties()
            Dim v = t.GetValue(obj, Nothing)
            If v Is Nothing Then
                Continue For
            End If
            If IsNumeric(v) Then
                sbSQL.Append(t.Name + ",")
            ElseIf v.GetHashCode <> 0 Then
                sbSQL.Append(t.Name + ",")
            End If
        Next
        sbSQL.Remove(sbSQL.Length - 1, 1)
        sbSQL.Append(") VALUES(")
        For Each p As System.Reflection.PropertyInfo In obj.GetType.GetProperties()
            Dim v = p.GetValue(obj, Nothing)
            If v Is Nothing Then
                Continue For
            End If
            If IsNumeric(v) Then
                sbSQL.Append(":" + p.Name + ",")
            ElseIf v.GetHashCode <> 0 Then
                sbSQL.Append(":" + p.Name + ",")
            End If
        Next
        sbSQL.Remove(sbSQL.Length - 1, 1).ToString()
        SQL = sbSQL.Append(")").ToString
        Return SQL
    End Function


    ''' <summary>
    ''' 生成删除SQL语句
    '''	create:YangJ
    ''' </summary>
    ''' <param name="objDTO">对象</param>
    ''' <param name="primaryKey">主键标识列(删除条件)</param>
    ''' <param name="value">主键标识列的值</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function CreateDeleteSQL(ByVal objDTO As Object, ByVal primaryKey As String, ByVal value As Object) As String
        Dim objName = objDTO.GetType.Name.ToString
        Dim sbSQL As New StringBuilder
        Dim SQL As String = ""
        sbSQL.Append("DELETE FROM " + GetTabName(objName) + " WHERE " + primaryKey + " = '" + value + "'")
        SQL = sbSQL.ToString
        Return SQL
    End Function

    ''' <summary>
    ''' 生成修改SQL语句
    '''	create:YangJ
    ''' </summary>
    ''' <param name="objDTO">对象</param>
    ''' <param name="primaryKey">主键标识列(修改条件)</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function CreateUpdateSQL(ByVal objDTO As Object, ByVal primaryKey As String) As String
        Dim objName = objDTO.GetType.Name.ToString
        Dim qq = objDTO.GetType.GetProperties()
        Dim sbSQL As New StringBuilder
        Dim SQL As String = ""
        sbSQL.Append("UPDATE " + GetTabName(objName) + " SET ")
        For Each t As System.Reflection.PropertyInfo In objDTO.GetType.GetProperties()
            Dim v = t.GetValue(objDTO, Nothing)
            If v Is Nothing Then '如果是空值，Update语句就将此值置空 modify by tianFW 2019年1月14日
                sbSQL.Append(t.Name + " = NULL,")
            ElseIf IsDate(v) Then '如果是默认时间且是最小值判断处理 add by tianFW 2019年1月15日
                If v = DateTime.MinValue Then
                    sbSQL.Append(t.Name + " = NULL,")
                End If
            ElseIf IsNumeric(v) Then
                sbSQL.Append(t.Name + " = :" + t.Name + ",")
            ElseIf v.GetHashCode <> 0 Then
                sbSQL.Append(t.Name + " = :" + t.Name + ",")
            End If
        Next
        sbSQL.Remove(sbSQL.Length - 1, 1)
        If primaryKey.Contains(" AND ") Then
            sbSQL.Append(primaryKey)
        Else
            sbSQL.Append(" WHERE " + primaryKey + "= :" + primaryKey)
        End If
        SQL = sbSQL.ToString
        Return SQL
    End Function


    ''' <summary>
    ''' 根据对象获得相对应Oracle表名并返回
    '''	create:YangJ
    ''' </summary>
    ''' <param name="str">对象实体类名称</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetTabName(ByVal str As String) As String
        Dim tabName As String = ""
        Select Case str
            Case "AttendanceRate"
                tabName = "AttendanceRate"
            Case "ResourceUseRate"
                tabName = "ResourceUseRate"
            Case "EconomicIndex"
                tabName = "EconomicIndex"
            Case "ProductFamilyMonthPlan"
                tabName = "ProductFamilyMonthPlan"
            Case "ProductFamilySingleInfo"
                tabName = "ProductFamilySingleInfo"
            Case "ProductFamilyFinishPlan" 'add zhunan 2018.12.20
                tabName = "ProductFamilyConsistInfo"
            Case "EmployeeDTO" 'addby tianfw 2019年1月5日
                tabName = "Employee"
            Case "MenuDefinitionDTO" 'addby tianfw 2019年1月8日
                tabName = "Menudefinition"
            Case "MenuitemDTO" 'addby tianfw 2019年1月8日
                tabName = "Menuitem"
            Case "RoleDTO" 'addby Wangjh 2019年1月8日
                tabName = "roledef"
        End Select
        Return tabName
    End Function


    ''' <summary>
    ''' 根据列属性的类型，返回相对应的Oracle数据库类型
    '''	create:YangJ
    ''' </summary>
    ''' <param name="tt">列属性的类型(字符串格式)</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function getOracleType(ByVal tt As String) As OracleType
        Dim ot As OracleType
        Select Case tt
            Case "System.String"
                ot = OracleType.VarChar
            Case "System.Integer"
                ot = OracleType.Number
            Case "System.DateTime"
                ot = OracleType.DateTime
            Case "System.Int32"
                ot = OracleType.Number
            Case "System.Decimal"
                ot = OracleType.Number
        End Select
        Return ot
    End Function

    ''' <summary>
    ''' 得到添加、修改中的参数数组并返回
    '''	create:YangJ
    ''' </summary>
    ''' <param name="dic">字典(属性名，属性值)</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function getPara(ByVal dic As Dictionary(Of String, Object), ByVal count As Int64) As OracleParameter()
        Dim para(count - 1) As OracleParameter
        Dim i As Integer = 0
        For Each d In dic
            Dim key = d.Key
            Dim v = d.Value
            Dim tt = v.GetType.ToString()
            para(i) = NewParameter(":" + key + "", v, getOracleType(tt))
            i += 1
        Next
        Return para
    End Function


    ''' <summary>
    ''' 根据传入的对象生成字典并返回
    '''	create:YangJ
    ''' </summary>
    ''' <param name="objDTO"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetDictionary(ByVal objDTO As Object) As Dictionary(Of String, Object)
        Dim dic As New Dictionary(Of String, Object)
        For Each t As System.Reflection.PropertyInfo In objDTO.GetType.GetProperties()
            Dim v = t.GetValue(objDTO, Nothing)
            If v Is Nothing Then
                Continue For
            End If
            If IsNumeric(v) Then
                dic.Add(t.Name.ToString, v)
            ElseIf v.GetHashCode <> 0 Then
                dic.Add(t.Name.ToString, v)
            End If
        Next
        Return dic
    End Function

#End Region

#Region "ExcuteDataBaseByEntity add;Wangjh 20211020"
    ''' <summary>
    ''' 查询数据
    ''' </summary>
    ''' <param name="excuteEntity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function QueryDataByEntity(ByVal excuteEntity As ExcuteEntity) As DataTable

        Using connection As New OracleConnection(connectionString)
            Dim dt As New DataTable

            connection.Open()

            Using command As New OracleCommand()
                Dim strSql = "", condition = ""

                If excuteEntity.ExcuteType = excuteEntity.ExcuteTypeEnum.selectAll Then
                    strSql = String.Concat("select * from ", excuteEntity.TableName, " where 1=1")
                Else
                    For Each field As FieldEntity In excuteEntity.ExcuteFileds
                        strSql += String.Concat(",", field.FieldName)
                    Next
                    strSql = strSql.Trim(",")
                    strSql = String.Concat("select ", strSql, " from ", excuteEntity.TableName, " where 1=1")
                End If

                If excuteEntity.WhereFileds IsNot Nothing Then
                    For Each field As FieldEntity In excuteEntity.WhereFileds
                        If String.IsNullOrWhiteSpace(field.Connector) Then
                            condition += String.Concat(" and ", field.FieldName, " =: ", field.FieldName)
                        Else
                            condition += String.Concat(" and ", field.FieldName, " ", field.Connector, " :", field.FieldName)
                        End If
                        Dim cmdPara = New OracleParameter(field.FieldName, field.FieldValue)

                        If field.FieldType = FieldEntity.FieldTypeEnum.Str Then
                            cmdPara.OracleType = OracleType.VarChar
                        ElseIf field.FieldType = FieldEntity.FieldTypeEnum.Numer Then
                            cmdPara.OracleType = OracleType.Int32
                        ElseIf field.FieldType = FieldEntity.FieldTypeEnum.DateTime Then
                            cmdPara.OracleType = OracleType.DateTime
                        End If
                        cmdPara.Direction = ParameterDirection.Input
                        command.Parameters.Add(cmdPara)
                    Next
                End If

                If Not String.IsNullOrWhiteSpace(excuteEntity.strWhere) Then
                    condition += excuteEntity.strWhere
                End If

                strSql = String.Concat(strSql, condition)

                command.Connection = connection
                command.CommandText = strSql
                command.CommandType = CommandType.Text

                Using da As OracleDataAdapter = New OracleDataAdapter(command)
                    da.Fill(dt)
                    command.Parameters.Clear()
                End Using
            End Using

            Return dt
        End Using
    End Function

    ''' <summary>
    ''' 生成条件
    ''' </summary>
    ''' <param name="field"></param>
    ''' <param name="cmd"></param>
    ''' <param name="thirdSql"></param>
    ''' <remarks></remarks>
    Shared Sub GenerateConditions(ByVal field As FieldEntity, ByVal cmd As OracleCommand, ByRef thirdSql As String)
        If field.Connector = "" Then
            thirdSql += String.Concat(" and ", field.FieldName, "=:", field.FieldName)
        Else
            thirdSql += String.Concat(" and ", field.FieldName, field.Connector, ":", field.FieldName)
        End If

        Dim cmdPara = New OracleParameter(field.FieldName, field.FieldValue)

        If field.FieldType = FieldEntity.FieldTypeEnum.Str Then
            cmdPara.OracleType = OracleType.VarChar
        ElseIf field.FieldType = FieldEntity.FieldTypeEnum.Numer Then
            cmdPara.OracleType = OracleType.Int32
        ElseIf field.FieldType = FieldEntity.FieldTypeEnum.DateTime Then
            cmdPara.OracleType = OracleType.DateTime
        End If
        If field.FieldValue Is Nothing Then
            cmdPara.Value = DBNull.Value
        End If
        cmdPara.Direction = ParameterDirection.Input
        cmd.Parameters.Add(cmdPara)

    End Sub

    ''' <summary>
    '''  添加cmd
    ''' </summary>
    ''' <param name="field"></param>
    ''' <param name="cmd"></param>
    ''' <remarks></remarks>
    Shared Sub addCmd(ByVal field As FieldEntity, ByVal cmd As OracleCommand)
        Dim cmdPara = New OracleParameter(field.FieldName, field.FieldValue)
        If field.FieldType = FieldEntity.FieldTypeEnum.Str Then
            cmdPara.OracleType = OracleType.VarChar
        ElseIf field.FieldType = FieldEntity.FieldTypeEnum.Numer Then
            cmdPara.OracleType = OracleType.Int32
        ElseIf field.FieldType = FieldEntity.FieldTypeEnum.DateTime Then
            cmdPara.OracleType = OracleType.DateTime
        ElseIf field.FieldType = FieldEntity.FieldTypeEnum.Clob Then
            cmdPara.OracleType = OracleType.Clob
        ElseIf field.FieldType = FieldEntity.FieldTypeEnum.Timestamp Then
            cmdPara.OracleType = OracleType.Timestamp
        End If
        If field.FieldValue Is Nothing Then
            cmdPara.Value = DBNull.Value
        End If
        cmdPara.Direction = ParameterDirection.Input
        cmd.Parameters.Add(cmdPara)
    End Sub


    ''' <summary>
    ''' 批量执行数据
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ExecuteDataByEntitys(ByVal listExuteEntity As List(Of ExcuteEntity)) As Boolean
        Using connection As New OracleConnection(connectionString)
                connection.Open()
            Using trans = connection.BeginTransaction()
                Using cmd As New OracleCommand()

                    Try

                  
                    For Each exuteEntity As ExcuteEntity In listExuteEntity
                        Dim strSql = "", sencondSql = "", thirdSql = ""
                        If exuteEntity.ExcuteType = ExcuteEntity.ExcuteTypeEnum.del Then
                            strSql = String.Concat("delete from ", exuteEntity.TableName)
                            If exuteEntity.WhereFileds Is Nothing And String.IsNullOrWhiteSpace(exuteEntity.strWhere) Then
                                Throw New Exception("删除数据请给条件")
                            End If

                            If exuteEntity.WhereFileds.Count = 0 And String.IsNullOrWhiteSpace(exuteEntity.strWhere) Then
                                Throw New Exception("删除数据请给条件")
                            End If

                            thirdSql = " where 1=1 "
                            If exuteEntity.WhereFileds IsNot Nothing Then
                                For Each field As FieldEntity In exuteEntity.WhereFileds
                                    GenerateConditions(field, cmd, thirdSql)
                                Next
                            End If

                        ElseIf exuteEntity.ExcuteType = ExcuteEntity.ExcuteTypeEnum.insert Then
                            If exuteEntity.ExcuteFileds Is Nothing Then
                                Throw New Exception("插入数据请给插入字段")
                            End If
                            If exuteEntity.ExcuteFileds.Count = 0 Then
                                Throw New Exception("插入数据请给插入字段")
                            End If
                            strSql = String.Concat("insert into ", exuteEntity.TableName)
                            sencondSql = " ("
                            Dim valuesSql = " values("
                            For Each field As FieldEntity In exuteEntity.ExcuteFileds
                                sencondSql += String.Concat(field.FieldName, ",")
                                valuesSql += String.Concat(":", field.FieldName, ",")
                                addCmd(field, cmd)
                            Next
                            sencondSql = sencondSql.Substring(0, sencondSql.Length - 1)
                            valuesSql = valuesSql.Substring(0, valuesSql.Length - 1)
                            sencondSql += String.Concat(")", valuesSql, ")")
                        ElseIf exuteEntity.ExcuteType = ExcuteEntity.ExcuteTypeEnum.update Then
                            If exuteEntity.ExcuteFileds Is Nothing Then
                                Throw New Exception("更新数据请给更新字段")
                            End If
                            If exuteEntity.ExcuteFileds.Count = 0 Then
                                Throw New Exception("更新数据请给更新字段")
                            End If

                            strSql = String.Concat("update ", exuteEntity.TableName)
                            sencondSql = " set "

                            For Each field As FieldEntity In exuteEntity.ExcuteFileds
                                sencondSql += String.Concat(field.FieldName, "=:", field.FieldName, ",")
                                addCmd(field, cmd)
                            Next
                            sencondSql = sencondSql.Substring(0, sencondSql.Length - 1)
                            '条件
                            thirdSql = " where 1=1 "
                            If exuteEntity.WhereFileds IsNot Nothing Then
                                For Each field As FieldEntity In exuteEntity.WhereFileds
                                    GenerateConditions(field, cmd, thirdSql)
                                Next
                            End If
                        End If
                        If Not String.IsNullOrWhiteSpace(exuteEntity.strWhere) Then
                            thirdSql += exuteEntity.strWhere
                        End If
                        strSql = String.Concat(strSql, sencondSql, thirdSql)
                        cmd.Connection = connection
                        cmd.CommandText = strSql
                        cmd.CommandType = CommandType.Text 'cmdType;
                        cmd.Transaction = trans

                        Dim rows = cmd.ExecuteNonQuery()
                        cmd.Parameters.Clear()
                    Next
                    '事务提交
                    trans.Commit()
                    Catch ex As Exception
                        trans.Rollback()
                        Throw New Exception(ex.Message)
                    Finally
                        cmd.Dispose()
                        connection.Dispose()
                    End Try

                End Using
            End Using

        End Using
        Return True
    End Function

    ''' <summary>
    ''' 执行数据
    ''' </summary>
    ''' <param name="exuteEntity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ExecuteDataByEntity(ByVal exuteEntity As ExcuteEntity) As Boolean
        Using connection As New OracleConnection(connectionString)
            connection.Open()
            Using cmd As New OracleCommand()
                Dim strSql = "", sencondSql = "", thirdSql = ""
                If exuteEntity.ExcuteType = ExcuteEntity.ExcuteTypeEnum.del Then
                    strSql = String.Concat("delete from ", exuteEntity.TableName)
                    If exuteEntity.WhereFileds Is Nothing And String.IsNullOrWhiteSpace(exuteEntity.strWhere) Then
                        Return False
                    End If

                    If exuteEntity.WhereFileds.Count = 0 And String.IsNullOrWhiteSpace(exuteEntity.strWhere) Then
                        Return False
                    End If

                    thirdSql = " where 1=1 "
                    If exuteEntity.WhereFileds IsNot Nothing Then
                        For Each field As FieldEntity In exuteEntity.WhereFileds
                            GenerateConditions(field, cmd, thirdSql)
                        Next
                    End If
                ElseIf exuteEntity.ExcuteType = ExcuteEntity.ExcuteTypeEnum.insert Then
                    If exuteEntity.ExcuteFileds Is Nothing Then
                        Return False
                    End If
                    If exuteEntity.ExcuteFileds.Count = 0 Then
                        Return False
                    End If
                    strSql = String.Concat("insert into ", exuteEntity.TableName)
                    sencondSql = " ("
                    Dim valuesSql = " values("
                    For Each field As FieldEntity In exuteEntity.ExcuteFileds
                        sencondSql += String.Concat(field.FieldName, ",")
                        valuesSql += String.Concat(":", field.FieldName, ",")
                        addCmd(field, cmd)
                    Next
                    sencondSql = sencondSql.Substring(0, sencondSql.Length - 1)
                    valuesSql = valuesSql.Substring(0, valuesSql.Length - 1)
                    sencondSql += String.Concat(")", valuesSql, ")")
                ElseIf exuteEntity.ExcuteType = ExcuteEntity.ExcuteTypeEnum.update Then
                    '必须要条件和更新字段
                    If exuteEntity.ExcuteFileds Is Nothing Then
                        Return False
                    End If
                    If exuteEntity.ExcuteFileds.Count = 0 Then
                        Return False
                    End If
                    If exuteEntity.WhereFileds Is Nothing And String.IsNullOrWhiteSpace(exuteEntity.strWhere) Then
                        Return False
                    End If

                    If exuteEntity.WhereFileds.Count = 0 And String.IsNullOrWhiteSpace(exuteEntity.strWhere) Then
                        Return False
                    End If
                    strSql = String.Concat("update ", exuteEntity.TableName)
                    sencondSql = " set "
                    For Each field As FieldEntity In exuteEntity.ExcuteFileds
                        sencondSql += String.Concat(field.FieldName, "=:", field.FieldName, ",")
                        addCmd(field, cmd)
                    Next
                    sencondSql = sencondSql.Substring(0, sencondSql.Length - 1)
                    thirdSql = " where 1=1 "
                    If exuteEntity.WhereFileds IsNot Nothing Then
                        For Each field As FieldEntity In exuteEntity.WhereFileds
                            GenerateConditions(field, cmd, thirdSql)
                        Next
                    End If
                End If

                If Not String.IsNullOrWhiteSpace(exuteEntity.strWhere) Then
                    thirdSql += exuteEntity.strWhere
                End If

                strSql = String.Concat(strSql, sencondSql, thirdSql)
                cmd.Connection = connection
                cmd.CommandText = strSql
                cmd.CommandType = CommandType.Text '//cmdType;

                Dim rows = cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()
                If rows > 0 Then
                    Return True
                Else
                    Return False
                End If
            End Using
        End Using
    End Function

    ''' <summary>
    ''' 查询单个数据
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function QuerySingleDataByEntity(ByVal fieldName As String, ByVal tableName As String, ByVal whereList As List(Of FieldEntity)) As Object
        Using connection As New OracleConnection(connectionString)
            Using cmd As New OracleCommand()
                connection.Open()
                Dim strSql = "select " + fieldName + " from " + tableName, condition = " where 1=1 "
                If whereList IsNot Nothing Then
                    For Each field As FieldEntity In whereList
                        GenerateConditions(field, cmd, condition)
                    Next
                End If
                strSql = String.Concat(strSql, condition)

                cmd.Connection = connection
                cmd.CommandText = strSql
                cmd.CommandType = CommandType.Text '//cmdType;

                Dim obj As Object = cmd.ExecuteScalar()
                '统计功能（一行一列）
                If ([Object].Equals(obj, Nothing)) OrElse ([Object].Equals(obj, System.DBNull.Value)) Then
                    Return Nothing
                Else
                    Return obj
                End If
            End Using
        End Using
    End Function
#End Region

#Region "77数据库操作"

    ''' <summary>
    ''' 执行SQL语句，返回影响的记录数
    ''' 例如数据添加、删除、更新操作
    ''' </summary>
    ''' <param name="SQLString">SQL语句</param>
    ''' <returns>影响的记录数</returns>
    Public Shared Function ExecuteSqlBy77(ByVal SQLString As String) As Integer
        Using connection As New OracleConnection(connectionString77)
            Using cmd As New OracleCommand(SQLString, connection)
                Try
                    connection.Open()
                    Dim rows As Integer = cmd.ExecuteNonQuery()
                    Return rows
                Catch E As OracleException
                    connection.Close()
                    Throw New Exception(E.Message)
                End Try
            End Using
        End Using
    End Function

    ''' <summary>
    ''' 执行多条SQL语句，实现数据库事务。
    ''' 当所有的执行语句全部执行成功，没有异常后才能进行事务的提交
    ''' </summary>
    ''' <param name="SQLStringList">多条SQL语句</param>		
    Public Shared Sub ExecuteSqlTranBy77(ByVal SQLStringList As ArrayList)
        Using conn As New OracleConnection(connectionString77)
            conn.Open()
            Dim cmd As New OracleCommand()
            cmd.Connection = conn
            Dim tx As OracleTransaction = conn.BeginTransaction()
            cmd.Transaction = tx
            Try
                For n As Integer = 0 To SQLStringList.Count - 1
                    Dim strsql As String = SQLStringList(n).ToString()
                    If strsql.Trim().Length > 1 Then
                        cmd.CommandText = strsql
                        cmd.ExecuteNonQuery()
                    End If
                Next
                '事务提交
                tx.Commit()
            Catch E As OracleException
                tx.Rollback()
                '事务回滚
                Throw New Exception(E.Message)
            End Try
        End Using
    End Sub

    ''' <summary>
    ''' 执行一条计算查询结果语句，返回查询结果（object）。
    ''' </summary>
    ''' <param name="SQLString">计算查询结果语句</param>
    ''' <returns>查询结果（object）</returns>
    Public Shared Function GetSingleBy77(ByVal SQLString As String) As Object
        Using connection As New OracleConnection(connectionString77)
            Using cmd As New OracleCommand(SQLString, connection)
                Try
                    connection.Open()
                    Dim obj As Object = cmd.ExecuteScalar()
                    '统计功能（一行一列）
                    If ([Object].Equals(obj, Nothing)) OrElse ([Object].Equals(obj, System.DBNull.Value)) Then
                        Return Nothing
                    Else
                        Return obj
                    End If
                Catch e As OracleException
                    connection.Close()
                    Throw New Exception(e.Message)
                End Try
            End Using
        End Using
    End Function
    ''' <summary>
    ''' 执行查询语句，返回DataTable
    ''' </summary>
    ''' <param name="SQLString">查询语句</param>
    ''' <returns>DataSet</returns>
    Public Shared Function Query_ReturnDatTableBy77(ByVal SQLString As String) As DataTable
        Using connection As New OracleConnection(connectionString77)
            Dim dt As New DataTable
            Try
                connection.Open()
                Dim command As New OracleDataAdapter(SQLString, connection)
                command.Fill(dt)
            Catch ex As OracleException
                Throw New Exception(ex.Message)
            End Try
            Return dt
        End Using
    End Function
#End Region

End Class

